﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Exam.DBHelper
{
    public class SQLHelper
    {
        protected string strConn = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

        public DataSet QueryDataSet(string strSql, List<SqlParameter> sqlParameterList)
        {
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(strConn))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
                    {
                        sqlCommand.CommandText = strSql;
                        if (sqlParameterList != null)
                        {
                            for (int i = 0; i < sqlParameterList.Count; i++)
                            {
                                sqlCommand.Parameters.Add(sqlParameterList[i]);
                            }
                        }
                        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                        DataSet dataSet = new DataSet();
                        sqlDataAdapter.Fill(dataSet);
                        return dataSet;
                    }
                }
            }
            catch { throw; }
        }

        public DataSet QueryDataSet(string strSql)
        {
            try
            {
                DataSet ds = QueryDataSet(strSql, null);

                return ds;
            }
            catch { throw; }
        }

        public DataTable QueryDataTable(string strSql)
        {
            try
            {
                DataTable dt = QueryDataTable(strSql, null);

                return dt;
            }
            catch { throw; }
        }

        public DataTable QueryDataTable(string strSql, List<SqlParameter> sqlParameterList)
        {
            try
            {
                DataTable dt = QueryDataSet(strSql, sqlParameterList).Tables[0];

                return dt;
            }
            catch { throw; }
        }

        public string QueryOneField(string strSql, List<SqlParameter> sqlParameterList)
        {
            try
            {
                DataTable dt = QueryDataTable(strSql, sqlParameterList);
                return dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : "";
            }
            catch { throw; }
        }

        public string QueryOneField(string strSql)
        {
            try
            {
                DataTable dt = QueryDataTable(strSql);
                return dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : "";
            }
            catch { throw; }
        }

        public List<string> QueryOneColumn(string strSql, List<SqlParameter> sqlParameterList)
        {
            try
            {
                DataTable dt = QueryDataTable(strSql, sqlParameterList);
                List<string> list = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    list.Add(dt.Rows[i][0].ToString());
                }
                return list;
            }
            catch { throw; }
        }

        public List<string> QueryOneColumn(string strSql)
        {
            try
            {
                DataTable dt = QueryDataTable(strSql);
                List<string> list = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    list.Add(dt.Rows[i][0].ToString());
                }
                return list;
            }
            catch { throw; }
        }

        public List<T> GetEntities<T>(string strSql, List<SqlParameter> listPar) where T : new()
        {
            try
            {
                DataTable dt;
                List<T> listClass = new List<T>();

                if (listPar != null)
                {
                    dt = QueryDataTable(strSql, listPar);
                }
                else
                {
                    dt = QueryDataTable(strSql);
                }

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    T typeClass = new T();
                    typeClass = Tools.SetPropertyValue<T>(dt, i);
                    listClass.Add(typeClass);
                }

                return listClass;
            }
            catch { throw; }
        }

        public List<T> GetEntities<T>(string strSql) where T : new()
        {
            try
            {
                return GetEntities<T>(strSql, null);
            }
            catch { throw; }
        }
        public List<T> GetEntities<T>(string strSql, int pagestart, int pagesize, out int totalcount) where T : new()
        {
            try
            {
                List<T> modellist = GetEntities<T>(strSql, null);
                totalcount = modellist.Count;
                if (pagestart + pagesize > totalcount)
                {
                    return modellist.GetRange(pagestart, totalcount - pagestart);
                }

                return modellist.GetRange(pagestart, pagesize);
            }
            catch { throw; }
        }

        public T GetEntity<T>(string strSql) where T : new()
        {
            try
            {
                List<T> list = GetEntities<T>(strSql, null);
                return list.Count > 0 ? list[0] : new T();
            }
            catch { throw; }
        }
        public int ExecuteNonQuerySQL(string strSql, List<SqlParameter> sqlParameterList)
        {
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(strConn))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
                    {
                        sqlCommand.CommandText = strSql;
                        if (sqlParameterList != null)
                        {
                            for (int i = 0; i < sqlParameterList.Count; i++)
                            {
                                sqlCommand.Parameters.Add(sqlParameterList[i]);
                            }
                        }
                        return sqlCommand.ExecuteNonQuery();
                    }
                }
            }
            catch { throw; }
        }

        public string getExistID(string tableName, string idname, Dictionary<string, SqlParameter> dictionary)
        {
            StringBuilder sbuilder = new StringBuilder("select ");
            sbuilder.Append(idname);
            sbuilder.Append(" from ");
            sbuilder.Append(tableName);
            sbuilder.Append(" where 1=1 ");
            List<SqlParameter> listpar = new List<SqlParameter>();
            foreach (var dic in dictionary)
            {
                sbuilder.Append(" and ");
                sbuilder.Append(dic.Key);
                sbuilder.Append("=@");
                sbuilder.Append(dic.Key);
                listpar.Add(dic.Value);
            }
            return QueryOneField(sbuilder.ToString(), listpar);
        }
        public T getExistData<T>(string tableName, Dictionary<string, SqlParameter> dictionary) where T : new()
        {
            StringBuilder sbuilder = new StringBuilder("select * from ");
            sbuilder.Append(tableName);
            sbuilder.Append(" where 1=1 ");
            List<SqlParameter> listpar = new List<SqlParameter>();
            foreach (var dic in dictionary)
            {
                sbuilder.Append(" and ");
                sbuilder.Append(dic.Key);
                sbuilder.Append("=@");
                sbuilder.Append(dic.Key);
                listpar.Add(dic.Value);
            }
            DataTable dt = QueryDataTable(sbuilder.ToString(), listpar);
            T typeClass = new T();
            if (dt != null && dt.Rows.Count > 0)
            {

                typeClass = Tools.SetPropertyValue<T>(dt, 0);
                return typeClass;
            }

            return typeClass;
        }

        public DataSet QueryProDataSet(string ProcName, SqlParameter[] ArrysqlParameter, out int TotalCount)
        {
            TotalCount = 0;
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(strConn))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
                    {
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandText = ProcName;

                        for (int i = 0; i < ArrysqlParameter.Length; i++)
                        {
                            sqlCommand.Parameters.Add(ArrysqlParameter[i]);
                        }

                        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                        DataSet dataSet = new DataSet();
                        sqlDataAdapter.Fill(dataSet);
                        TotalCount = (int)sqlCommand.Parameters["@TotalCount"].Value;
                        return dataSet;
                    }
                }
            }
            catch { throw; }
        }
    }
}
